# 导入包
from matplotlib import pyplot as plt
import pandas as pd
import statsmodels.formula.api as smf
from sqlalchemy import create_engine
import pymysql
import statsmodels.api as sm
import numpy as np
import seaborn as sns

db_config = {
    'host': '127.0.0.1',
    'user': 'root',
    'password': 'root',
    'database': 'tushare',
    'port': 3306,
    'charset': 'utf8mb4'   # 添加字符集设置
}
engine = create_engine(
    f"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db_config['database']}?charset={db_config['charset']}"
)
conn = pymysql.connect(**db_config)
chunk_size = 10000
df = pd.read_sql_query("""
    SELECT d.* FROM date_1 d WHERE d.trade_date BETWEEN '2023-01-01' and '2023-12-31' and d.ts_code = '000001.SZ'
    """,
    conn,
    chunksize=chunk_size)
df1 = pd.concat(df, ignore_index=True)

df1['zd_close']=df1['closes'].shift(1)

df1['zd_closes']=round((df1['i_closes']-df1['i_closes'].shift(1))/df1['i_closes'].shift(1),2)

df1=df1.dropna(subset=['zd_close','zs_closes']).reset_index(drop=True)

numeric_cols=df1.select_dtypes(include=['number'].columns.tolist())
print(df1.head)

X=df1[['buy_lg_vol','sell_lg_vol','sekk_elg_vol','net_mf_vol','zs_closes']]

eigenvalues,eigenvectors=np.linalg.eig(np.cov(X,rowvar=False))
print('累计贡献率为:',round(eigenvalues[:5].sum()/eigenvalues.sum(),4)*100,'%')

n_components=5
top_eigenvectors=eigenvectors[:, :n_components]

principal_components=np.dot(X,top_eigenvectors)

principal_components=np.dot(X,top_eigenvectors)
data_pca=pd.concat([df1,pd.DataFrame(principal_components,
                    columns=[f'PC{i+1}' for i in range(n_components)])],axis=1)

X_pca=data_pca[[f'PC{i+1}' for i in range(n_components)]].copy()
X_pca=sm.add_constant(X_pca)

y=df1['zd_close'].copy()

model_pca=sm.OLS(y,X_pca)

result_pca=model_pca.fit()

print("\n回归模型结果:")
print(result_pca.summary())

X_pca_selected=data_pca[['PC1','PC2','PC3']]

X_pca_selected.columns=['PC1','PC2','PC3']

X_pca_selected=sm.add_constant(X_pca_selected)

